﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.Common;

namespace GroupProject
{
    public partial class frmOrders : Form
    {
        SqlConnection con;
        SqlDataAdapter da;
        DataTable dt;
        int code = 1;

        public frmOrders()
        {
            InitializeComponent();
           // InitialoizeADOObject();
            code = 1;
            loadOrders();
            loadOrderDetail();
           
        }



        void loadOrders()
        {
            con = new SqlConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["SqlStr"].ConnectionString;

            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                dgvOrders.Columns[17].Visible = false;
                switch (code)
                {
                    case 1:
                        cmd.CommandText = "sp_SelectFromtblORders";
                        break;
                    case 2:
                        cmd.CommandText = "sp_SearchByCustomer";
                        SqlParameter para = new SqlParameter("@customer", SqlDbType.NVarChar, 40);
                        para.Value = cboFilter.Text;
                        cmd.Parameters.Add(para);
                        break;
                    case 3:
                        cmd.CommandText = "sp_SearchByDate";
                        SqlParameter param= new SqlParameter("@dateFrom", SqlDbType.DateTime);
                        param.Value = dtpSearchFrom.Value.ToShortDateString();
                        cmd.Parameters.Add(param);
                        param = new SqlParameter("@dateTo", SqlDbType.DateTime);
                        param.Value = dtpSearchTo.Value.ToShortDateString();
                        cmd.Parameters.Add(param);
                        break;
                    case 4:
                        cmd.CommandText = "sp_SearchByProduct";
                        SqlParameter paramenter = new SqlParameter("@product", SqlDbType.NVarChar);
                        paramenter.Size = 40;
                        paramenter.Value = cboFilter.Text;
                        cmd.Parameters.Add(paramenter);
                        dgvOrders.Columns[17].Visible = true;
                        break;
                    default:
                        break;
                }
                //cmd.CommandText = "sp_SelectFromtblORders";
                SqlDataReader dr = cmd.ExecuteReader();
                dgvOrders.Rows.Clear();
                if (code != 4)
                {
                    while (dr.Read())
                    {
                        dgvOrders.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), dr[4].ToString(), dr[5].ToString(),
                            dr[6].ToString(), dr[7].ToString(), dr[8].ToString(), dr[9].ToString(), dr[10].ToString(), dr[11].ToString(), dr[12].ToString()
                            , dr[13].ToString(), dr[14].ToString(), dr[15].ToString(), dr[16].ToString());
                    }
                }
                else
                {
                    while (dr.Read())
                    {
                        dgvOrders.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), dr[4].ToString(), dr[5].ToString(),
                            dr[6].ToString(), dr[7].ToString(), dr[8].ToString(), dr[9].ToString(), dr[10].ToString(), dr[11].ToString(), dr[12].ToString()
                            , dr[13].ToString(), dr[14].ToString(), dr[15].ToString(), dr[16].ToString(), dr[17].ToString());
                    }
                }
                dr.Close();

                dgvOrders.Columns["custid"].Visible = false;
                dgvOrders.Columns["custid"].DisplayIndex = 14;// ban dau la 1
                dgvOrders.Columns["Customer"].DisplayIndex = 1; // ban dau: 14
                dgvOrders.Columns["empid"].Visible = false;
                dgvOrders.Columns["empid"].DisplayIndex = 15; //ban dau 2
                dgvOrders.Columns["Employee"].DisplayIndex = 2;
                dgvOrders.Columns["shipperid"].Visible = false;
                dgvOrders.Columns["shipperid"].DisplayIndex = 16;//band dau 6
                dgvOrders.Columns["Shipper"].DisplayIndex = 6;
                // index ban dau cua cac cot ko thay doi
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message);
            }
            finally
            {
                con.Close();
                
            }
        }

        private void loadOrderDetailWhenOrderClick()
        {
            con = new SqlConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["SqlStr"].ConnectionString;
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_SelectOrderDetailByID";
                SqlParameter para = new SqlParameter("@orderid", SqlDbType.Int);
                String temp = txtOrderID.Text;
                para.Value = temp;
                cmd.Parameters.Add(para);
                SqlDataReader dr = cmd.ExecuteReader();
                dgvOrderDetail.Rows.Clear();
                while (dr.Read())
                {
                    dgvOrderDetail.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(),
                        dr[3].ToString(), dr[4].ToString(), dr[5].ToString());
                }
                dr.Close();
                dgvOrderDetail.Columns[1].Visible = false;
                dgvOrderDetail.Columns[1].DisplayIndex = 5;
                dgvOrderDetail.Columns[5].DisplayIndex = 1;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);

            }
            finally
            {
                con.Close();
            }
        }


        private void loadComboBox()
        {
            //load customer
            con = new SqlConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["SqlStr"].ConnectionString;
            da = new SqlDataAdapter("Select * from Sales.Customers", con);
            dt = new DataTable("Customers");
            da.Fill(dt);
            cboCustomer.DataSource = dt;
            cboCustomer.DisplayMember = "companyname";
            cboCustomer.ValueMember = "custid";
            
            //load employee
            da = new SqlDataAdapter("select * from HR.Employees", con);
            dt = new DataTable("Employees");
            da.Fill(dt);
            cboEmployee.DataSource = dt;
            cboEmployee.DisplayMember = "lastname";
            cboEmployee.ValueMember = "empid";
            //load shipper
            da = new SqlDataAdapter("Select * from Sales.Shippers", con);
            dt = new DataTable("Shipper");
            da.Fill(dt);
            cboShipper.DataSource = dt;
            cboShipper.DisplayMember = "companyname";
            cboShipper.ValueMember = "shipperid";

            //load OrderId
            da = new SqlDataAdapter("Select * from Sales.Orders", con);
            dt = new DataTable("OrderID");
            da.Fill(dt);
            cboOrderID.DataSource = dt;
            cboOrderID.DisplayMember = "orderid";
            cboOrderID.ValueMember = "orderid";

            //load productname;
            da = new SqlDataAdapter("select * from Production.Products", con);
            dt = new DataTable("ProductID");
            da.Fill(dt);
            cboProduct.DataSource = dt;
            cboProduct.DisplayMember = "productname";
            cboProduct.ValueMember = "productid";

           
           
        }


        private bool validateData()
        {
            bool check = false;
            if (cboEmployee.SelectedIndex == -1)
            {
                errorProvider1.SetError(cboEmployee, "You must select!");
                check = true;
            }
            if (dtpOrderDate.Checked == false)
            {
                errorProvider1.SetError(dtpOrderDate, "You must choose the date");
                check = true;
            }
            if (dtpRequiredDate.Checked == false)
            {
                errorProvider1.SetError(dtpRequiredDate, "You must choose the date");
                check = true;
            }

            if (dtpShippedDate.Value < dtpOrderDate.Value && dtpShippedDate.Checked == true)
            {
                errorProvider1.SetError(dtpShippedDate, "Value of ship date must greater than order date");
                check = true;
            }
            if (dtpRequiredDate.Value < dtpOrderDate.Value)
            {
                errorProvider1.SetError(dtpRequiredDate, "Value of required date must greater than order date");
            }
            if (cboShipper.SelectedIndex == -1)
            {
                errorProvider1.SetError(cboShipper, "You must select shipper");
                check = true;
            }
            double money;
            bool tmp = Double.TryParse(txtFreight.Text.Trim(), out money);
            if (tmp == false)
            {
                errorProvider1.SetError(txtFreight, "Freight must be number");
                check = true;
            }
            else
            {
                money = double.Parse(txtFreight.Text.Trim());
                if (money < 0)
                {
                    errorProvider1.SetError(txtFreight, "Freight must be greater than 0");
                    check = true;
                }
            }
            if (txtShipName.Text.Trim().Length == 0)
            {
                errorProvider1.SetError(txtShipName, "Ship name must not be blank");
                check = true;
            }
            if (txtShipName.Text.Trim().Length > 40)
            {
                errorProvider1.SetError(txtShipName, "Length must less than 40");
                check = true;
            }
            if (txtShipAddr.Text.Trim().Length == 0)
            {
                errorProvider1.SetError(txtShipAddr, "Ship name must not be blank");
                check = true;
            }
            if (txtShipAddr.Text.Trim().Length > 60)
            {
                errorProvider1.SetError(txtShipAddr, "Length must less than 60");
                check = true;
            }
            if (txtShipCity.Text.Trim().Length == 0)
            {
                errorProvider1.SetError(txtShipCity, "Ship name must not be blank");
                check = true;
            }
            if (txtShipCity.Text.Trim().Length > 15)
            {
                errorProvider1.SetError(txtShipCity, "Length must less than 15");
                check = true;
            }
            if (txtShipRegion.Text.Trim().Length > 15)
            {
                errorProvider1.SetError(txtShipRegion, "Length must less than 15");
                check = true;
            }
            if (mtxtShipPostalCode.Text.Trim().Length < 5)
            {
                errorProvider1.SetError(mtxtShipPostalCode, "You must fill full");
                check = true;
            }
            if (cboShipCountry.SelectedIndex == -1)
            {
                errorProvider1.SetError(cboShipCountry, "You must fill ship country");
                check = true;
            }
            if (check == true)
            {
                return false;
            }
            else errorProvider1.Clear();
            return true;
        }


        private bool validateOrderDetail()
        {
            bool check = false;
            if (cboOrderID.SelectedIndex  == -1)
            {
                errorProvider2.SetError(cboOrderID, "You must select!");
                check = true;
            }
            if (cboProduct.SelectedIndex == -1)
            {
                errorProvider2.SetError(cboProduct, "You must select!");
                check = true;
            }
            double price;
            bool tmp = Double.TryParse(txtUnitprice.Text.Trim(), out price);
            if (tmp == false)
            {
                errorProvider2.SetError(txtUnitprice, "Price must be number");
                check = true;
            }
            else
            {
                price = double.Parse(txtUnitprice.Text.Trim());
                if (price < 0)
                {
                    errorProvider2.SetError(txtUnitprice, "Price must be positive");
                    check = true;
                }
            }
            tmp = double.TryParse(txtDiscount.Text.Trim(), out price);
            if (tmp == false)
            {
                errorProvider2.SetError(txtDiscount, "Discount must be number");
                check = true;
            }
            else
            {
                price = double.Parse(txtDiscount.Text.Trim());
                if (price < 0 || price > 1)
                {
                    errorProvider2.SetError(txtDiscount, "Discount must be positive and smaller than 1");
                    check = true;
                }
            }
            int qty;
            tmp = int.TryParse(txtQuantity.Text.Trim(), out qty);
            if (tmp == false)
            {
                errorProvider2.SetError(txtQuantity, "Quantity must be number");
                check = true;
            }
            else
            {
                qty = int.Parse(txtQuantity.Text.Trim());
                if (qty < 0)
                {
                    errorProvider2.SetError(txtQuantity, "Quantity must be positive");
                    check = true;
                }
            }
            if (check == true)
            {
                return false;
            }
            else
            {
                errorProvider2.Clear();
                return true;
            }
        }


        private void addOrders()
        {
            con = new SqlConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["SqlStr"].ConnectionString;
            con.Open();
            try
            {
                
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_InsertTotblOrders";
                SqlParameter param = new SqlParameter("@custid", SqlDbType.Int);
                if (cboCustomer.SelectedIndex == -1)
                {
                    param.Value = DBNull.Value;
                } else
                param.Value = cboCustomer.SelectedValue.ToString();
                cmd.Parameters.Add(param);

                param = new SqlParameter("@empid", SqlDbType.Int);
                param.Value = cboEmployee.SelectedValue.ToString();
                cmd.Parameters.Add(param);

                param = new SqlParameter("@orderdate", SqlDbType.DateTime);
                param.Value = dtpOrderDate.Value.ToShortDateString();
                cmd.Parameters.Add(param);

                param = new SqlParameter("@requireddate", SqlDbType.DateTime);
                param.Value = dtpRequiredDate.Value.ToShortDateString();
                cmd.Parameters.Add(param);

                param = new SqlParameter("@shippeddate", SqlDbType.DateTime);
                if (dtpShippedDate.Checked == false)
                    param.Value = DBNull.Value;
                else
                param.Value = dtpShippedDate.Value.ToShortDateString();
                cmd.Parameters.Add(param);

                param = new SqlParameter("@shipperid", SqlDbType.Int);
                param.Value = cboShipper.SelectedValue.ToString();
                cmd.Parameters.Add(param);

                param = new SqlParameter("@freight", SqlDbType.Money);
                param.Value = txtFreight.Text.Trim();
                cmd.Parameters.Add(param);

                param = new SqlParameter("@shipname", SqlDbType.NVarChar);
                param.Size = 40;
                param.Value = txtShipName.Text.Trim();
                cmd.Parameters.Add(param);

                param = new SqlParameter("@shipaddress", SqlDbType.NVarChar);
                param.Size = 60;
                param.Value = txtShipAddr.Text.Trim();
                cmd.Parameters.Add(param);

                param = new SqlParameter("@shipcity", SqlDbType.NVarChar);
                param.Size = 15;
                param.Value = txtShipCity.Text.Trim();
                cmd.Parameters.Add(param);

                param = new SqlParameter("@shipregion", SqlDbType.NVarChar);
                if (txtShipRegion.Text.Length == 0)
                {
                    param.Size = 15;
                    param.Value = DBNull.Value;
                }
                else
                {
                    param.Size = 15;
                    param.Value = txtShipRegion.Text.Trim();
                }
                cmd.Parameters.Add(param);

                param = new SqlParameter("@shippostalcode", SqlDbType.NVarChar);
                if (mtxtShipPostalCode.Text.Length == 0)
                {
                    param.Size = 10;
                    param.Value = DBNull.Value;
                }
                else
                {
                    param.Size = 10;
                    param.Value = mtxtShipPostalCode.Text.Trim();
                }
                cmd.Parameters.Add(param);

                param = new SqlParameter("@shipcountry", SqlDbType.NVarChar);
                param.Size = 15;
                param.Value = cboShipCountry.Text.Trim();
                cmd.Parameters.Add(param);

                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                con.Close();
            }
        }


        private void addDetails()
        {
            con = new SqlConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["SqlStr"].ConnectionString;
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            try
            {
                con.Open();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_InsertTotblOrderDetails";
                SqlParameter param = new SqlParameter("@orderid", SqlDbType.Int);
                param.Value = cboOrderID.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@productid", SqlDbType.Int);
                param.Value = cboProduct.SelectedValue.ToString();
                cmd.Parameters.Add(param);

                param = new SqlParameter("@unitprice", SqlDbType.Money);
                param.Value = txtUnitprice.Text.Trim();
                cmd.Parameters.Add(param);

                param = new SqlParameter("@qty", SqlDbType.SmallInt);
                param.Value = txtQuantity.Text.Trim();
                cmd.Parameters.Add(param);

                param = new SqlParameter("@discount", SqlDbType.Real);
                param.Value = txtDiscount.Text.Trim();
                cmd.Parameters.Add(param);

                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message);
            }
            finally
            {
                con.Close();
            }
        }

        private void frmOrders_Load(object sender, EventArgs e)
        {
           
            loadComboBox();
            clearField();
            clearFieldDetail();
        }


        private void updateOrders()
        {
            if (dgvOrders.SelectedRows.Count > 0)
            {
                con = new SqlConnection();
                con.ConnectionString = ConfigurationManager.ConnectionStrings["SqlStr"].ConnectionString;
                con.Open();
                try
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "sp_UpdatetblOrders";
                    SqlParameter param = new SqlParameter("@orderid", SqlDbType.Int);
                    param.Value = txtOrderID.Text.Trim();
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@custid", SqlDbType.Int);

                    if (cboCustomer.SelectedIndex == -1)
                    {
                        param.Value = DBNull.Value;
                    }
                    else
                        param.Value = cboCustomer.SelectedValue.ToString();
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@empid", SqlDbType.Int);
                    param.Value = cboEmployee.SelectedValue.ToString();
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@orderdate", SqlDbType.DateTime);
                    param.Value = dtpOrderDate.Value.ToShortDateString();
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@requireddate", SqlDbType.DateTime);
                    param.Value = dtpRequiredDate.Value.ToShortDateString();
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@shippeddate", SqlDbType.DateTime);
                    if (dtpShippedDate.Checked == false)
                        param.Value = DBNull.Value;
                    else
                        param.Value = dtpShippedDate.Value.ToShortDateString();
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@shipperid", SqlDbType.Int);
                    param.Value = cboShipper.SelectedValue.ToString();
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@freight", SqlDbType.Money);
                    param.Value = txtFreight.Text.Trim();
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@shipname", SqlDbType.NVarChar);
                    param.Size = 40;
                    param.Value = txtShipName.Text.Trim();
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@shipaddress", SqlDbType.NVarChar);
                    param.Size = 60;
                    param.Value = txtShipAddr.Text.Trim();
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@shipcity", SqlDbType.NVarChar);
                    param.Size = 15;
                    param.Value = txtShipCity.Text.Trim();
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@shipregion", SqlDbType.NVarChar);
                    if (txtShipRegion.Text.Length == 0)
                    {
                        param.Size = 15;
                        param.Value = DBNull.Value;
                    }
                    else
                    {
                        param.Size = 15;
                        param.Value = txtShipRegion.Text.Trim();
                    }
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@shippostalcode", SqlDbType.NVarChar);
                    if (mtxtShipPostalCode.Text.Length == 0)
                    {
                        param.Size = 10;
                        param.Value = DBNull.Value;
                    }
                    else
                    {
                        param.Size = 10;
                        param.Value = mtxtShipPostalCode.Text.Trim();
                    }
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@shipcountry", SqlDbType.NVarChar);
                    param.Size = 15;
                    param.Value = cboShipCountry.Text;
                    cmd.Parameters.Add(param);

                    cmd.ExecuteNonQuery();

                }
                catch (Exception ex)
                {

                    MessageBox.Show(ex.Message);
                }
                finally
                {
                    con.Close();
                }
            }
        }


        private void clearField()
        {
            txtOrderID.Clear();
            cboCustomer.SelectedIndex = -1;
            cboEmployee.SelectedIndex = -1;
            dtpOrderDate.Value = DateTime.Now;
            dtpRequiredDate.Value = DateTime.Now;
            dtpShippedDate.Value = DateTime.Now;
            cboShipper.SelectedIndex = -1;
            txtFreight.Clear();
            txtShipName.Clear();
            txtShipAddr.Clear();
            txtShipCity.Clear();
            txtShipRegion.Clear();
            mtxtShipPostalCode.Clear();
            cboShipCountry.SelectedIndex = -1;


        }


        private void deleteOrder()
        {
            if (dgvOrders.SelectedRows.Count > 0)
            {
                con = new SqlConnection();
                con.ConnectionString = ConfigurationManager.ConnectionStrings["SqlStr"].ConnectionString;
                con.Open();
                try
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "sp_DeleteOrders";
                    SqlParameter param = new SqlParameter("@orderid", SqlDbType.Int);
                    param.Value = txtOrderID.Text;
                    cmd.Parameters.Add(param);
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    
                }
                finally
                {
                    con.Close();
                }
            }
        }


        private void loadOrderDetail()
        {
            con = new SqlConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["SqlStr"].ConnectionString;
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_SelectFromTblORderDetail";
                SqlDataReader dr = cmd.ExecuteReader();
                dgvOrderDetail.Rows.Clear();
                while (dr.Read())
                {
                    dgvOrderDetail.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(),
                        dr[3].ToString(), dr[4].ToString(), dr[5].ToString());
                }
                dr.Close();
                dgvOrderDetail.Columns[1].Visible = false;
                dgvOrderDetail.Columns[1].DisplayIndex = 5;
                dgvOrderDetail.Columns[5].DisplayIndex = 1;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);

            }
            finally
            {
                con.Close();
            }
        }


        private void UpdateOrderDetail()
        {
            if (dgvOrderDetail.SelectedRows.Count > 0)
            {
                con = new SqlConnection();
                con.ConnectionString = ConfigurationManager.ConnectionStrings["SqlStr"].ConnectionString;
                try
                {
                    con.Open();
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "sp_UpdatetblOrderDetails";
                    SqlParameter param = new SqlParameter("@orderid", SqlDbType.Int);
                    param.Value = cboOrderID.Text;
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@productid", SqlDbType.Int);
                    param.Value = cboProduct.SelectedValue.ToString();
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@unitprice", SqlDbType.Money);
                    param.Value = txtUnitprice.Text.Trim();
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@qty", SqlDbType.SmallInt);
                    param.Value = txtQuantity.Text.Trim();
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@discount", SqlDbType.Real);
                    param.Value = txtDiscount.Text.Trim();
                    cmd.Parameters.Add(param);

                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);

                }
                finally
                {
                    con.Close();
                }
            }
        }


        private void deleteOrderDetail()
        {
            if (dgvOrderDetail.SelectedRows.Count > 0)
            {
                con = new SqlConnection();
                con.ConnectionString = ConfigurationManager.ConnectionStrings["SqlStr"].ConnectionString;
                try
                {
                    con.Open();
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "sp_DeleteOrderDetail";
                    SqlParameter param = new SqlParameter("@orderid", SqlDbType.Int);
                    param.Value = cboOrderID.SelectedValue.ToString();
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@productid", SqlDbType.Int);
                    param.Value = cboProduct.SelectedValue.ToString();
                    cmd.Parameters.Add(param);

                    cmd.ExecuteNonQuery();
           
                }
                catch (Exception ex)
                {

                    MessageBox.Show(ex.Message);
                }
                finally
                {
                    con.Close();
                }
            }
        }


        private void clearFieldDetail()
        {
            cboOrderID.SelectedIndex = -1;
            cboProduct.SelectedIndex = -1;
            txtUnitprice.Clear();
            txtDiscount.Clear();
            txtQuantity.Clear();
        }

        private void dgvOrders_SelectionChanged(object sender, EventArgs e)
        {
            if (dgvOrders.SelectedRows.Count > 0)
            {
                btnUpdate.Enabled = true;
                btnDelete.Enabled = true;
                btnAdd.Enabled = false;
                try
                {
                    DataGridViewRow r = dgvOrders.SelectedRows[0];
                    txtOrderID.Text = r.Cells[0].Value.ToString();
                    if (r.Cells[14].Value == DBNull.Value) cboCustomer.SelectedIndex = -1;
                    else
                        cboCustomer.Text = r.Cells[14].Value.ToString();
                    cboEmployee.Text = r.Cells[15].Value.ToString();
                    DateTime dt;
                    DateTime.TryParse(r.Cells[3].Value.ToString(), out dt);
                    dtpOrderDate.Value = dt;
                    DateTime.TryParse(r.Cells[4].Value.ToString(), out dt);
                    dtpRequiredDate.Value = dt;
                    if (r.Cells[5].Value.ToString().Length == 0) dtpShippedDate.Checked = false;
                    else
                    {
                        DateTime.TryParse(r.Cells[5].Value.ToString(), out dt);
                        dtpShippedDate.Value = dt;
                    }
                    cboShipper.Text = r.Cells[16].Value.ToString();
                    txtFreight.Text = r.Cells[7].Value.ToString();
                    txtShipName.Text = r.Cells[8].Value.ToString();
                    txtShipAddr.Text = r.Cells[9].Value.ToString();
                    txtShipCity.Text = r.Cells[10].Value.ToString();
                    txtShipRegion.Text = r.Cells[11].Value.ToString();
                    mtxtShipPostalCode.Text = r.Cells[12].Value.ToString();
                    cboShipCountry.Text = r.Cells[13].Value.ToString();
                    loadOrderDetailWhenOrderClick();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    
                }
            }
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            if (!validateData())
            {
                return;
            }
            updateOrders();
            loadOrders();
            clearField();
            MessageBox.Show("Update successful");
        }


        private void btnAdd_Click(object sender, EventArgs e)
        {
            if (validateData() == false)
            {
                return;
            }
            addOrders();
            loadOrders();
            loadComboBox();
            MessageBox.Show("Add successful!");
            clearField();
        }

        private void btnNew_Click(object sender, EventArgs e)
        {
            btnAdd.Enabled = true;
            btnDelete.Enabled = false;
            btnUpdate.Enabled = false;
            clearField();
            loadComboBox();
            groupBox2.Visible = false;
            dgvOrderDetail.Visible = false;
            btnOpenDetail.Visible = true;
            clearField();
            clearFieldDetail();
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            DialogResult res = MessageBox.Show("Are you sure?", "Confirm", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            if (res == DialogResult.Yes)
            {
                deleteOrder();
                clearField();
                loadOrders();
                loadComboBox();
                MessageBox.Show("Done!");    
            }

        }

        private void txtFilter_Click(object sender, EventArgs e)
        {
            if (rdCustomer.Checked == true)
            {
                code = 2;
                loadOrders();
            }
            if(rdProduct.Checked == true)
            {
                code = 4;
                loadOrders();
            }
            if (rdProduct.Checked == false && rdCustomer.Checked == false)
            {
                MessageBox.Show("Please choose option to filter");
            }
            
        }

        private void btnReload_Click(object sender, EventArgs e)
        {
            code = 1;
            loadOrders();
            loadComboBox();
            loadOrderDetail();
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            code = 3;
            loadOrders();
        }

        private void btnUpdateDetail_Click(object sender, EventArgs e)
        {
            if (!validateOrderDetail()) return;
            UpdateOrderDetail();
            loadOrderDetail();
            MessageBox.Show("Update successful");
            clearFieldDetail();
            loadComboBox();
        }

        private void btnDeleteDetail_Click(object sender, EventArgs e)
        {
            DialogResult res = MessageBox.Show("Are you sure?", "Confirm", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            if (res == DialogResult.Yes)
            {
                deleteOrderDetail();
                clearField();
                loadOrderDetail();
                MessageBox.Show("Done!");
                loadComboBox();
                clearFieldDetail();
            }
        }

        private void dgvOrderDetail_SelectionChanged(object sender, EventArgs e)
        {
            if (dgvOrderDetail.SelectedRows.Count > 0)
            {
                btnAddDetail.Enabled = false;
                btnDeleteDetail.Enabled = true;
                btnUpdateDetail.Enabled = true;
                txtUnitprice.Enabled = false;
                //cboOrderID.Enabled = false;
                //cboProduct.Enabled = false;
                DataGridViewRow r = dgvOrderDetail.SelectedRows[0];
                cboOrderID.Text = r.Cells[0].Value.ToString();
                cboProduct.Text = r.Cells[5].Value.ToString();
                txtUnitprice.Text = r.Cells[2].Value.ToString();
                txtQuantity.Text = r.Cells[3].Value.ToString();
                txtDiscount.Text = r.Cells[4].Value.ToString();

                cboOrderID.Enabled = false;
                cboProduct.Enabled = false;
            }
        }

        private void btnNewDetail_Click(object sender, EventArgs e)
        {
            btnAddDetail.Enabled = true;
            btnDeleteDetail.Enabled = false;
            btnUpdateDetail.Enabled = false;
            txtUnitprice.Enabled = true;
            cboOrderID.Enabled = true;
            cboProduct.Enabled = true;
            loadComboBox();
            clearFieldDetail();
        }

        private void btnAddDetail_Click_1(object sender, EventArgs e)
        {
            if (!validateOrderDetail())
            {
                return;
            }
            addDetails();
            loadOrderDetail();
            MessageBox.Show("Add successful!");
            clearFieldDetail();
            loadComboBox();
        }

        private void rdCustomer_Click(object sender, EventArgs e)
        {
            if (rdCustomer.Checked == true)
            {
                //load Filter
                con = new SqlConnection();
                con.ConnectionString = ConfigurationManager.ConnectionStrings["SqlStr"].ConnectionString;
                da = new SqlDataAdapter("Select * from Sales.Customers", con);
                dt = new DataTable("FilterCustomer");
                da.Fill(dt);
                cboFilter.DataSource = dt;
                cboFilter.DisplayMember = "companyname";
                cboFilter.ValueMember = "custid";
            }
            else cboFilter.DataSource = null;
        }

        private void rdProduct_Click(object sender, EventArgs e)
        {
            if (rdProduct.Checked == true)
            {
                //load Filter
                con = new SqlConnection();
                con.ConnectionString = ConfigurationManager.ConnectionStrings["SqlStr"].ConnectionString;
                da = new SqlDataAdapter("select * from Production.Products", con);
                dt = new DataTable("FilterProduct");
                da.Fill(dt);
                cboFilter.DataSource = dt;
                cboFilter.DisplayMember = "productname";
                cboFilter.ValueMember = "productid";
            }
            else cboFilter.DataSource = null;
        }

        private void btnOpenDetail_Click(object sender, EventArgs e)
        {
            groupBox2.Visible = true;
            dgvOrderDetail.Visible = true;
            loadOrderDetail();
            loadComboBox();
            btnOpenDetail.Visible = false;
        }
    }

}
